package cn.edu.thsis.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.sun.corba.se.impl.orb.ParserTable.TestAcceptor1;

import cn.edu.thsis.dao.UserDao;
import cn.edu.thsis.pojo.User;
import cn.edu.thsis.pojo.student;
import cn.edu.thsis.pojo.teacher;
import cn.edu.thsis.pojo.thsis;

public class UserDaoImpl extends BaseDao implements UserDao{	
	//根据用户名和密码查询用户信息
	@Override
	public User chexkUserLoginDao(String uid, String pwd, String type) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		User u = null;
		try {
			conn = jdbcUtils.getConnection();
			String sql = "select * from "+type+" where uid=? and pwd=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1,uid);
			ps.setString(2,pwd);
			rs=ps.executeQuery();
			  while (rs.next()) {
				u=new User();
				u.setUid(rs.getString("uid"));
				u.setUname(rs.getString("uname"));
				u.setPwd(rs.getString("pwd"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//关闭资源
				if(rs != null) rs.close();
				if(ps != null) ps.close();
				if(conn != null) conn.close();
				}
		//返回结果
		return u;
	}

	@Override
		public User findUserDao(String uid, String type) throws SQLException {
			//声明jdbc对象
					Connection conn = null;
					PreparedStatement ps = null;
					ResultSet rs = null;
					//声明变量
					student stu = null;
					teacher tea = null;
					try {
						conn = jdbcUtils.getConnection();
						//创建sql命令
						String sql = "select * from "+type+" where uid=?";
						//创建sql命令对象
						ps=conn.prepareStatement(sql);
						//给占位符赋值
						ps.setString(1, uid);
						//执行sql
						rs=ps.executeQuery();
						//遍历结果
						
						if (type.equals("student")) {
							while (rs.next()) {
								//给变量赋值
								stu=new student();
								stu.setUid(rs.getString("uid"));
								stu.setUname(rs.getString("uname"));
								stu.setPwd(rs.getString("pwd"));
								stu.setMajor(rs.getString("major"));
								stu.setStu_tel(rs.getString("stu_tel"));
								stu.setStu_email(rs.getString("stu_email"));
							}
						}else {
							tea=new teacher();
							tea.setUid(rs.getString("uid"));
							tea.setUname(rs.getString("uname"));
							tea.setPwd(rs.getString("pwd"));
							tea.setTea_title(rs.getString("tea_title"));
							tea.setTea_tel(rs.getString("tea_tel"));
						}
						
					} catch (Exception e) {
						e.printStackTrace();
					}finally {
						//关闭资源
							rs.close();
							ps.close();
							conn.close();
					}
					//返回结果
					if (type.equals("student")) {
						return stu;
					}else {
						return tea;
					}
		}
//根据用户ID修改用户密码
	@Override
	public int userChangePwdDao(String newPwd, String uid ,String type) throws SQLException {
		//声明jdbc对象
		Connection conn = null;
		PreparedStatement ps = null;
		//创建变量
		int index = -1;
		//加载驱动
		try {
			conn = jdbcUtils.getConnection();
			//创建sql命令
			String sql = "update "+type+" set pwd=? where uid=?";
			//创建SQL命令对象
			ps = conn.prepareStatement(sql);
			//给占位符赋值
			ps.setString(1,newPwd);
			ps.setString(2,uid);
			//执行
			index = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {//关闭资源
				ps.close();
				conn.close();
		}
		//返回结果
		return index;
	}
	
	//获取老师信息
	public List<teacher> teaShowDao() throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<teacher> tealist =null;
		try {
			conn = jdbcUtils.getConnection();
			String sql = "select * from teacher";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			tealist = new ArrayList<teacher>();
				while (rs.next()) {
				User tea=new teacher();
				tea.setUid(rs.getString("uid"));
				tea.setUname(rs.getString("uname"));
				((teacher) tea).setTea_title(rs.getString("tea_title"));
				((teacher) tea).setTea_tel(rs.getString("tea_tel"));
				tealist.add((teacher) tea);
				}	
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
				rs.close();
				ps.close();
				conn.close();
		}
			return tealist;
	}
	
	//添加教师信息
	@Override
	public int addteaDao(teacher teahr) {
		Connection conn = null;
		PreparedStatement ps = null;
		int index = -1;
		try {
			conn = jdbcUtils.getConnection();
			String sql = "insert into teacher values(?,?,default,?,?,default)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, teahr.getUid());
			ps.setString(2,teahr.getUname());
			ps.setString(3,teahr.getTea_title());
			ps.setString(4,teahr.getTea_tel());
			index=ps.executeUpdate();
			ps.close();
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			//关闭资源
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		//返回结果
			return index;
	}
	
	//添加学生信息
	@Override
	public int addstuDao(student stud) {
		Connection conn = null;
		PreparedStatement ps = null;
		int index = -1;
		try {
			conn = jdbcUtils.getConnection();
			String sql = "insert into student values(?,?,default,?,?,?,default)";
			ps = conn.prepareStatement(sql);
			ps.setString(1,stud.getUid());
			ps.setString(2,stud.getUname());
			ps.setString(3,stud.getMajor());
			ps.setString(4,stud.getStu_tel());
			ps.setString(5,stud.getStu_email());
			index=ps.executeUpdate();
			ps.close();
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			//关闭资源
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		//返回结果
			return index;
			}
	//获取学生信息
	@Override
	public List<student> stuShowDao() throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<student> stulist =null;
		try {
			conn = jdbcUtils.getConnection();
			String sql = "select * from student";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			stulist = new ArrayList<student>();
				while (rs.next()) {
				User stu=new student();
				stu.setUid(rs.getString("uid"));
				stu.setUname(rs.getString("uname"));
				((student) stu).setMajor(rs.getString("major"));
				((student) stu).setStu_tel(rs.getString("stu_tel"));
				((student) stu).setStu_email(rs.getString("stu_email"));
				stulist.add((student) stu);
				}	
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//关闭资源
				rs.close();
				ps.close();
				conn.close();
		}
		//返回结果
			return stulist;
	}

	//删除教师信息
	@Override
	public int delteaDao(String uid) throws SQLException {
		int index =0;
		String sql = "delete from teacher where uid='"+uid+"'";
		Connection conn =jdbcUtils.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		index = ps.executeUpdate();
		return index;
	}
	
	//删除学生信息
	@Override
	public int delstuDao(String uid) throws SQLException {
		int index =0;
		String sql = "delete from student where uid='"+uid+"'";
		Connection conn =jdbcUtils.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		index = ps.executeUpdate();
		return index;
	}
	
	//学生预约注册论题
	@Override
	public int doEnrolDao(String thsis_id, String stu_id) throws SQLException {
		int index = 0;
		String sql = "insert into enrol(stu_id,thsis_id) values('"+stu_id+"','"+thsis_id+"')";
		Connection conn = jdbcUtils.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		index = ps.executeUpdate();
		return index;
	}
	
	//判断学生是否已预约该论题
	public boolean hasLogin(String stu_id,String thsis_id) throws SQLException {
		boolean f = false;
		String sql = "SELECT en.stu_id,en.thsis_id from enrol en WHERE en.stu_id='"+stu_id+"' AND en.thsis_id='"+thsis_id+"'";
		Connection conn = jdbcUtils.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		try {
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {f = true;}
			else {f = false;}
		} catch (Exception e) {
			e.getMessage();
		}return f;
	}
	
	//得到已预约本论题学生列表
	@Override
	public List<student> doAcceptDao(String thsis_id) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<student> stulist =null;
		try {
			conn = jdbcUtils.getConnection();
			String sql = "select s.uid,s.uname,s.major,s.stu_tel,s.stu_email,en.stu_id from student s,enrol en where s.uid=en.stu_id AND en.thsis_id=" + thsis_id +"";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			stulist = new ArrayList<student>();
				while (rs.next()) {
				User stu=new student();
				stu.setUid(rs.getString("uid"));
				stu.setUname(rs.getString("uname"));
				((student) stu).setMajor(rs.getString("major"));
				((student) stu).setStu_tel(rs.getString("stu_tel"));
				((student) stu).setStu_email(rs.getString("stu_email"));
				stulist.add((student) stu);
				}	
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//关闭资源
				rs.close();
				ps.close();
				conn.close();
		}
		//返回结果
			return stulist;
	}

	//添加论题
	@Override
	public int addthDao(thsis the) {
		Connection conn = null;
		PreparedStatement ps = null;
		int index = -1;
		try {
			conn = jdbcUtils.getConnection();
			String sql = "insert into thsis values(?,?,?,?,default,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1,the.getThsis_id());
			ps.setString(2,the.getThsis_name());
			ps.setString(3,the.getThsis_kind());
			ps.setString(4,the.getThsis_class());
			ps.setString(5,the.getTea_id());
			index=ps.executeUpdate();
			ps.close();
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
			return index;
			}

	//查看未选题的学生
	@Override
	public List<student> stuthDao() throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<student> stuth =null;
		try {
			conn = jdbcUtils.getConnection();
			String sql = "SELECT s.uid,s.uname,s.major,s.stu_tel,s.stu_email FROM student s LEFT JOIN enrol en ON (s.uid = en.stu_id)WHERE en.stu_id IS NULL";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			stuth = new ArrayList<student>();
				while (rs.next()) {
				User stu=new student();
				stu.setUid(rs.getString("uid"));
				stu.setUname(rs.getString("uname"));
				((student) stu).setMajor(rs.getString("major"));
				((student) stu).setStu_tel(rs.getString("stu_tel"));
				((student) stu).setStu_email(rs.getString("stu_email"));
				stuth.add((student) stu);
				}	
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
				rs.close();
				ps.close();
				conn.close();
		}
			return stuth;
	}
	
	//查看未出题的教师
	@Override
	public List<teacher> teathDao() throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<teacher> teath =null;
		try {
			conn = jdbcUtils.getConnection();
			String sql = "SELECT t.uid,t.uname,t.tea_title,t.tea_tel FROM teacher t LEFT JOIN thsis th ON (t.uid = th.tea_id)WHERE th.tea_id IS NULL";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			teath = new ArrayList<teacher>();
				while (rs.next()) {
				User tea=new teacher();
				tea.setUid(rs.getString("uid"));
				tea.setUname(rs.getString("uname"));
				((teacher) tea).setTea_title(rs.getString("tea_title"));
				((teacher) tea).setTea_tel(rs.getString("tea_tel"));
				teath.add((teacher) tea);
				}	
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
				rs.close();
				ps.close();
				conn.close();
		}
			return teath;
	}

	//接受学生选择该论题
	@Override
	public int okDao(String thsis_id,String stu_id) throws SQLException {
		int index = 0;
		Connection conn =jdbcUtils.getConnection();
		String sql = "UPDATE enrol en,thsis th SET en.accept = 2,th.`select`=2 WHERE en.thsis_id=th.thsis_id AND en.thsis_id='"+thsis_id+"' and en.stu_id ='"+stu_id+"'";
		PreparedStatement ps = conn.prepareStatement(sql);
		index = ps.executeUpdate();
		return index;
	}

	//教师查看自己的论题
	@Override
	public List<thsis> mythDao(String uid) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<thsis> mythsis =null;
		try {
			conn = jdbcUtils.getConnection();
			String sql = "select * from thsis where tea_id =" + uid + "";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			mythsis = new ArrayList<thsis>();
				while (rs.next()) {
					thsis th = new thsis();
					th.setThsis_id(rs.getString("thsis_id"));
					th.setThsis_name(rs.getString("thsis_name"));
					th.setThsis_kind(rs.getString("thsis_kind"));
					th.setThsis_class(rs.getString("thsis_class"));
					mythsis.add(th);
				}	
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//关闭资源
				rs.close();
				ps.close();
				conn.close();
		}
		//返回结果
			return mythsis;
	}

	//拒绝学生选择该论题
	@Override
	public int noDao(String thsis_id, String stu_id) throws SQLException {
		int index = 0;
		Connection conn =jdbcUtils.getConnection();
		String sql = "UPDATE enrol SET accept = '3' WHERE thsis_id ='"+thsis_id+"' and stu_id ='"+stu_id+"'";
		PreparedStatement ps = conn.prepareStatement(sql);
		index = ps.executeUpdate();
		return index;
	}

	//催促学生选题
	@Override
	public int warnstu(String id) throws SQLException {
		int index = 0;
		Connection conn =jdbcUtils.getConnection();
		String sql = "UPDATE student SET warn = 2 where uid ='"+id+"'";
		PreparedStatement ps = conn.prepareStatement(sql);
		index = ps.executeUpdate();
		return index;
	}

	//催促教师出题
	@Override
	public int warnteaDao(String id) throws SQLException {
		int index = 0;
		Connection conn =jdbcUtils.getConnection();
		String sql = "UPDATE teacher SET warn = 2 where uid ='"+id+"'";
		PreparedStatement ps = conn.prepareStatement(sql);
		index = ps.executeUpdate();
		return index;
	}

	//查看所有论题信息
	@Override
	public List<thsis> allthesdao() throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<thsis> thes =null;
		try {
			conn = jdbcUtils.getConnection();
			String sql = "SELECT th.thsis_name,thsis_class,th.thsis_kind,t.uname,t.tea_title,t.tea_tel FROM thsis th,teacher t WHERE th.tea_id=t.uid";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			thes = new ArrayList<thsis>();
				while (rs.next()) {
					thsis th = new thsis();
					th.setThsis_name(rs.getString("thsis_name"));
					th.setUname(rs.getString("uname"));
					th.setThsis_class(rs.getString("thsis_class"));
					th.setTea_title(rs.getString("tea_title"));
					th.setThsis_kind(rs.getString("thsis_kind"));
					th.setTea_tel(rs.getString("tea_tel"));
					thes.add(th);
				}	
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//关闭资源
				rs.close();
				ps.close();
				conn.close();
		}
		//返回结果
			return thes;
	}

	
	
	
	
	
	
}
